package pers.ht.mbpo.dao;

import pers.ht.mbpo.bean.Competitor;
import pers.ht.mbpo.bean.User;
import pers.ht.mbpo.util.DBUtil;
import pers.ht.mbpo.util.DaoUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CompetitorDao {
    //添加
    public void insert(Competitor c) {
        String sql = "INSERT INTO competitor(stuNumber,matchId) VALUES (?,?)";
        DaoUtil.update(sql, c.getStuNumber(), c.getMatchId());
    }

    //删除
    public void delete(String id, String matchId) {
        String sql = "DELETE FROM competitor WHERE stuNumber = ? and matchId = ?";
        DaoUtil.update(sql, id, matchId);
    }

    //查询个数
    public int queryNum(String matchId, String username) {
        String sql = "select count(*) num from competitor where matchId = '" + matchId + "' ";
        if (username != null) {
            sql += " AND stuNumber = '" + username + "'";
        }
        int num = 0;
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
            while (rs.next()) {
                num = rs.getInt("num");
            }
            DBUtil.close(rs, stat, conn);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return num;
    }

    public List<User> queryUser(String matchId) {
        String sql = "select u.username,u.password,u.nickname from user u JOIN competitor c on u.username = c.stuNumber JOIN matchs m on m.matchId = c.matchId where c.matchId = '" + matchId + "'";
        List<User> users = new ArrayList<User>();
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
            while (rs.next()) {
                User u = new User();
                u.setUsername(rs.getString("u.username"));
                u.setPassword(rs.getString("u.password"));
                u.setNickname(rs.getString("u.nickname"));
                users.add(u);
            }
            DBUtil.close(rs, stat, conn);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return users;
    }
}
